package pms.qa.service;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import pms.common.value.ProjectValue;
import pms.common.value.QaEmailValue;
import pms.common.value.QaListValue;
import pms.common.value.QaSearchValue;
import pms.common.dao.QaListDao;
import pms.common.dao.ProjectDao;
import pms.common.dao.ListSGDao;


public class QaListServiceImpl implements QaListService {

	private JdbcTemplate jdbcTemplate;
	private QaListDao qaListDao;
	private ListSGDao listSGDao;
	private ProjectDao projectDao;

	public ListSGDao getListSGDao() {
		return listSGDao;
	}

	public void setListSGDao(ListSGDao listSGDao) {
		this.listSGDao = listSGDao;
	}

	public ProjectDao getProjectDao() {
		return projectDao;
	}

	public void setProjectDao(ProjectDao projectDao) {
		this.projectDao = projectDao;
	}

	public QaListDao getQaListDao() {
		return qaListDao;
	}

	public void setQaListDao(QaListDao qaListDao) {
		this.qaListDao = qaListDao;
	}
	
	//プロジェクト
	public List<ProjectValue> getProjectList(){
		return projectDao.getProjectList();
	}
	// 所属テーマ
	@SuppressWarnings("unchecked")
	public List<HashMap> getFunctionArray() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {   
        return listSGDao.getFunctionArray();
    }
	// 提出者、問合先
	@SuppressWarnings("unchecked")
	public List<HashMap> getAskedArray() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {   
		List<HashMap> l_array = new ArrayList();
		l_array = listSGDao.getUserData();
		return l_array;
    }
	
	// 状態
	@SuppressWarnings("unchecked")
	public List<HashMap> getStsArray() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {   
		List<HashMap> l_array = new ArrayList();
        SqlRowSet l_rs = listSGDao.getMasterData("qa_status");
        while(l_rs.next()) {
        	HashMap l_hashMap = new HashMap();
        	l_hashMap.put("master_id",l_rs.getString("MASTER_ID").trim());
           l_hashMap.put("master_group",l_rs.getString("MASTER_GROUP").trim());
           l_hashMap.put("master_value",l_rs.getString("MASTER_VALUE").trim());
        	l_array.add(l_hashMap);
        }
        return l_array;
    }
	@SuppressWarnings("unchecked")
	public ArrayList getListData(QaSearchValue l_search) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{
		ArrayList qaliststmp = new ArrayList();
		QaListValue qalist = new QaListValue();
		SqlRowSet l_rs = qaListDao.getListData(l_search);
		HashMap proList = listSGDao.getProject();
		HashMap themeList = listSGDao.getTheme();
		while(l_rs.next()) {
    		qalist = new QaListValue();
    		qalist.setBanGo(l_rs.getString("QA_NO").trim());
    		if(proList.containsKey(String.valueOf(l_rs.getString("PROJECTID").trim()))){
    			qalist.setProject(String.valueOf(proList.get(l_rs.getString("PROJECTID").trim())));
    		} else {
    			qalist.setProject(l_rs.getString("PROJECTID").trim());
    		}
    		if(themeList.containsKey(String.valueOf(l_rs.getString("TEMA").trim()))){
    			qalist.setTema(String.valueOf(themeList.get(l_rs.getString("TEMA").trim())));
    		} else {
    			qalist.setTema(l_rs.getString("TEMA").trim());
    		}
    		qalist.setDankai(l_rs.getString("QA_LEVEL").trim());
    		qalist.setKasho(l_rs.getString("QA_PART").trim());
    		qalist.setGaIYo(l_rs.getString("QA_SUMMARY").trim());
    		qalist.setInputDate(l_rs.getString("QA_START_DATE").trim().substring(0,4)+"/"+l_rs.getString("QA_START_DATE").trim().substring(4,6)+"/"+l_rs.getString("QA_START_DATE").trim().substring(6));
    		qalist.setAsker(l_rs.getString("QA_FROM").trim());
    		qalist.setLimmitDate(l_rs.getString("QA_LIMIT_DATE").trim().substring(0,4)+"/"+l_rs.getString("QA_LIMIT_DATE").trim().substring(4,6)+"/"+l_rs.getString("QA_LIMIT_DATE").trim().substring(6));
    		qalist.setStatus(l_rs.getString("QA_STATUS").trim());
    		qalist.setEmergencyDegree(l_rs.getString("QA_KINKYUU").trim());
    		qalist.setToIAWaSeSaKi(l_rs.getString("QA_TO").trim());
    		qaliststmp.add(qalist);
		}
		return qaliststmp;
	}

	public boolean deleteData(String[] banGos) {
		
		boolean l_flg = qaListDao.deleteData(banGos);
		return l_flg;
	}

	public boolean sendMail(String[] banGos) {
		System.out.println("senMail start");
		try {
			for (int i = 0; i < banGos.length; i++) {
				QaEmailValue qaEmailValue= qaListDao.getEmailData(banGos[i]);
				// default account information
				// String smtpServer = server;
				// String smtpServer = "210.22.128.203";
				// String smtpServer = "192.168.5.94";
				String smtpAuth = "true";
				// String From = mailFrom;
				// String To = mailTo;
				// String Subject = mailSubject;
				// String Text = mailBody;
				// String ccNIT = "chenyue@hyron.com";
				// String ccHyron = "chenyue@hyron.com";
				// String bym = "baoyiming@hyron.com";
				// String gao = "gaohelie@hyron.com";
				// Address[] ccAddress = { new InternetAddress(ccNIT),// new
				// // InternetAddress(ccNIT),
				// new InternetAddress(ccHyron) };

				Properties props = new Properties();
				Session sendMailSession;
				Transport transport;
				props.put("mail.smtp.host", qaEmailValue.getSmtpServer());
				props.put("mail.smtp.auth", smtpAuth);

				sendMailSession = Session.getInstance(props);

				// Debug
				sendMailSession.setDebug(true);
				Message newMessage = new MimeMessage(sendMailSession);
				newMessage.setFrom(qaEmailValue.getMailFrom());
				newMessage.setRecipient(Message.RecipientType.TO, qaEmailValue
						.getMailTo());
				newMessage.setRecipients(Message.RecipientType.CC, qaEmailValue
						.getCcAddress());
				newMessage.setSubject(qaEmailValue.getMailSubject());
				newMessage.setSentDate(new Date());
				Multipart multipart = new MimeMultipart();
				MimeBodyPart messageBodyPart1 = new MimeBodyPart();
				messageBodyPart1.setText(qaEmailValue.getMailBody());
				multipart.addBodyPart(messageBodyPart1);
				// Put parts in message
				newMessage.setContent(multipart);

				// java.io.File dir = new java.io.File(path);
				// System.out.println("path: " + path);
				// System.out.println("name: " + fileName);
				// if (dir.exists() && fileName != null && fileName != "") {
				// MimeBodyPart messageBodyPart = new MimeBodyPart();
				// DataSource source = new FileDataSource(path);
				// messageBodyPart.setDataHandler(new DataHandler(source));
				// fileName = MimeUtility.encodeText(fileName, "Shift_JIS",
				// "B");
				// messageBodyPart.setFileName(fileName);
				//
				// multipart.addBodyPart(messageBodyPart);
				// }
				newMessage.saveChanges();
				transport = sendMailSession.getTransport("smtp");
				transport.connect(qaEmailValue.getSmtpServer(), "gaohelie", "omron11");
				transport.sendMessage(newMessage, newMessage
						.getRecipients(Message.RecipientType.TO));
				transport.sendMessage(newMessage, newMessage
						.getRecipients(Message.RecipientType.CC));
			}
		} catch (Exception mailEx) {
			System.err.println("Send Mail Error:" + mailEx.getMessage());
			return false;
		}
		System.out.println("senMail end");
		return true;
	}

//	public InputStream expData(String[] banGos) throws Exception {
//		String sr[] = new String[18];
//		String title[] = new String[18];
//		short length[] = new short[18];
//		// TITLE名
//		title[0] = "項番";
//		title[1] = "段階";
//		title[2] = "ｻﾌﾞｼｽﾃﾑ";
//		title[3] = "発生箇所";
//		title[4] = "機能";
//		title[5] = "質問概要";
//		title[6] = "質問詳細";
//		title[7] = "提出日付";
//		title[8] = "提出者";
//		title[9] = "回答期限";
//		title[10] = "緊急度";
//		title[11] = "問合先";
//		title[12] = "回答内容";
//		title[13] = "回答日付";
//		title[14] = "確認者";
//		title[15] = "確認日付";
//		title[16] = "状態";
//		title[17] = "備考";
//		// wookBookの列の幅
//		length[0] = 8;
//		length[1] = 8;
//		length[2] = 12;
//		length[3] = 12;
//		length[4] = 24;
//		length[5] = 24;
//		length[6] = 48;
//		length[7] = 12;
//		length[8] = 8;
//		length[9] = 12;
//		length[10] = 8;
//		length[11] = 8;
//		length[12] = 48;
//		length[13] = 12;
//		length[14] = 8;
//		length[15] = 12;
//		length[16] = 12;
//		length[17] = 24;
//		// wookbookを生成
//		HSSFWorkbook wb = new HSSFWorkbook();
//		// sheetを生成
//		HSSFSheet sheet = wb.createSheet("new sheet");
//		// 書式設定
//		HSSFCellStyle style = wb.createCellStyle();
//		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//		style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
//		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
//		HSSFRow rowTitle = sheet.createRow(0);
//		int j = 1;
//		for (int a = 0; a < banGos.length; a++) {
//			SqlRowSet l_value = getExpData(banGos[a]);
//			HSSFRow row = sheet.createRow(j);
//			j++;
//			while (l_value.next()) {
//				sr[0] = l_value.getString("QA_NO");
//				sr[1] = l_value.getString("QA_LEVEL");
//				sr[2] = l_value.getString("QA_SUB_SYSTEM");
//				sr[3] = l_value.getString("QA_PART");
//				sr[4] = l_value.getString("QA_FUNCTION");
//				sr[5] = l_value.getString("QA_SUMMARY").replaceAll("&lt;br&gt",
//						"\r\n");
//				sr[6] = l_value.getString("QA_QUESTION").replaceAll(
//						"&lt;br&gt", "\r\n");
//				sr[7] = l_value.getString("QA_START_DATE");
//				sr[8] = l_value.getString("QA_FROM");
//				sr[9] = l_value.getString("QA_LIMIT_DATE");
//				sr[10] = l_value.getString("QA_KINKYUU");
//				sr[11] = l_value.getString("QA_TO");
//				sr[12] = l_value.getString("QA_ANSWER").replaceAll("&lt;br&gt",
//						"\r\n");
//				sr[13] = l_value.getString("QA_ANSWER_DATE");
//				sr[14] = l_value.getString("QA_CONFIRMER");
//				sr[15] = l_value.getString("QA_CONFIRM_DATE");
//				sr[16] = l_value.getString("QA_STATUS");
//				sr[17] = l_value.getString("QA_MEMO").replaceAll("&lt;br&gt",
//						"\r\n");
//				for (int i = 0; i < 18; i++) {
//					// CellのVALUEを設定
//					HSSFCell cellTitle = rowTitle.createCell((short) i);
//					cellTitle.setCellValue(title[i]);
//					cellTitle.setCellStyle(style);
//					sheet.setColumnWidth((short) i, (short) (length[i] * 256));
//					HSSFCell cell = row.createCell((short) i);
//					// 日付の書式を設定
//					Workbook formatbook = new Workbook();
//					HSSFDataFormat format = new HSSFDataFormat(formatbook);
//					style.setDataFormat(format.getFormat("m/d/yy"));
//					cell.setCellStyle(style);
//					cell.setCellValue(sr[i]);
//				}
//			}
//		}
////		FileOutputStream fileOut = new FileOutputStream("c:\\workbook.xls");
////		wb.write(fileOut);
//
//		byte wbbyte[] = wb.getBytes();
//		InputStream downis = new ByteArrayInputStream(wbbyte,0,wbbyte.length);
//
//		System.out.println("FileOutputStream OK");
////		fileOut.close();
//		return downis;
//	}
	public InputStream expExcelData(String[] banGos) throws Exception {
		InputStream excelStream;
		HashMap proList = listSGDao.getProject();
		HashMap themeList = listSGDao.getTheme();
        StringBuffer excelBuf = new StringBuffer();
        excelBuf.append("項番\t");
        excelBuf.append("プロジェクト\t");
        excelBuf.append("テーマ名\t");
        excelBuf.append("段階\t");
        excelBuf.append("発生箇所\t");
        excelBuf.append("質問概要\t");
        excelBuf.append("質問詳細\t");
        excelBuf.append("提出日付\t");
        excelBuf.append("提出者\t");
        excelBuf.append("回答期限\t");
        excelBuf.append("緊急度\t");
        excelBuf.append("問合先\t");
        excelBuf.append("回答内容\t");
        excelBuf.append("回答日付\t");
        excelBuf.append("確認者\t");
        excelBuf.append("確認日付\t");
        excelBuf.append("状態\t");
        excelBuf.append("備考\n");
        
        for (int a = 0; a < banGos.length; a++) {
			SqlRowSet l_value = qaListDao.getExpData(banGos[a]);
			while (l_value.next()) {
				excelBuf.append(l_value.getString("QA_NO")+"\t");
				if(proList.containsKey(String.valueOf(l_value.getString("PROJECTID").trim()))){
	    			excelBuf.append(proList.get(l_value.getString("PROJECTID").trim())+"\t");
	    		} else {
	    			excelBuf.append(l_value.getString("PROJECTID").trim()+"\t");
	    		}
	    		if(themeList.containsKey(String.valueOf(l_value.getString("TEMA").trim()))){
	    			excelBuf.append(themeList.get(l_value.getString("TEMA").trim())+"\t");
	    		} else {
	    			excelBuf.append(l_value.getString("TEMA").trim()+"\t");
	    		}
				excelBuf.append(l_value.getString("QA_LEVEL")+"\t");
				excelBuf.append(l_value.getString("QA_PART")+"\t");
				excelBuf.append("\"" + l_value.getString("QA_SUMMARY").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\t");
				excelBuf.append("\"" + l_value.getString("QA_QUESTION").replaceAll(
						"&lt;br&gt", "\r\n") + "\"" + "\t");
				excelBuf.append(l_value.getString("QA_START_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_FROM")+"\t");
				excelBuf.append(l_value.getString("QA_LIMIT_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_KINKYUU")+"\t");
				excelBuf.append(l_value.getString("QA_TO")+"\t");
				excelBuf.append("\"" + l_value.getString("QA_ANSWER").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("QA_ANSWER_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_CONFIRMER")+"\t");
				excelBuf.append(l_value.getString("QA_CONFIRM_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_STATUS")+"\t");
				excelBuf.append("\"" +l_value.getString("QA_MEMO").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\n");
			}
		}
        String excelString = excelBuf.toString();
        excelStream = new ByteArrayInputStream(excelString.getBytes());
        return excelStream;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

}
